package com.ce.pms.charge.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ce.pms.base.service.impl.PmsBaseServiceImpl;
import com.ce.pms.charge.dao.IMeterDataRegisterDao;
import com.ce.pms.charge.service.IExcelExportService;
import com.ce.pms.house.dao.IBuildingDao;
import com.ce.pms.house.dao.IFlatDao;
import com.ce.pms.house.dao.IMeterDao;
import com.ce.pms.house.model.BuildingDomain;
import com.ce.pms.house.model.ProjectDomain;
import com.ce.util.BlankUtil;
import com.ce.util.DateUtil;

public class ExcelExportServiceImpl extends PmsBaseServiceImpl implements IExcelExportService
{


	/**
	 * 方法描述: 创建单个的HSSFSheet头
	 * @author 胡煦
	 * Create on:2008-8-26
	 * @param HSSFRow row,HSSFCell cell
	 * @return HSSFSheet
	 * Edit author:
	 * Edit on:
	 * 修改原因：
	 */
    private HSSFCell creatSheetHead(HSSFRow row,int i, HSSFWorkbook workbook){
    	HSSFCell cell;
    	//创建一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        //style.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER ); //居中对齐
        HSSFFont font = workbook.createFont();
     	// 设置字体颜色
     	font.setColor(HSSFFont.COLOR_RED);
     	// 设置字体大小
     	font.setFontHeight((short)240);
     	font.setBoldweight((short)480);
     	style.setFont(font);
     	//设置表头
		cell = row.createCell((short) i++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    cell.setCellStyle(style);
		cell.setCellValue("建设期");
		cell = row.createCell((short) i++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style);
		cell.setCellValue("楼栋");
		cell = row.createCell((short) i++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style);
		cell.setCellValue("楼层");
		cell = row.createCell((short) i++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style);
		cell.setCellValue("房号");
		cell = row.createCell((short) i++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style);
		cell.setCellValue("上次读数");
		cell = row.createCell((short) i++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style);
		cell.setCellValue("本次读数");
		cell = row.createCell((short) i++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style);
		cell.setCellValue("抄表时间");
		return cell;
    }
	/**
	 * 方法描述: 创建单个的HSSFSheet
	 * @author 胡煦
	 * Create on:2008-8-26
	 * @param List,ProjectDomain,BuildingDomain,meterType,workbook
	 * @return HSSFSheet
	 * Edit author:
	 * Edit on:
	 * 修改原因：
	 */
    private HSSFSheet creatSheet(List<Object[]> list,String projectName,
    		Integer buildingSeqNo,String meterType, HSSFWorkbook workbook,String currentDateStr) {

    	//创建一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        //style.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直对齐
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平对齐
        HSSFFont font = workbook.createFont();
     	// 设置字体大小
     	font.setFontHeight((short)220);
     	font.setBoldweight((short)400);
     	style.setFont(font);

		// 新建一名为"meterType"的工作表，其语句为：
		HSSFSheet sheet = workbook.createSheet(meterType);
		//设置列宽
		sheet.setColumnWidth((short) 0,(short)(( 42*5)/((double)1/20))) ;
		sheet.setColumnWidth((short) 6,(short)(( 38*5)/((double)1/20))) ;
		// 在索引0的位置创建行（最顶端的行）
		HSSFRow row = sheet.createRow((short) 0);
		// 在索引0的位置创建单元格（左上端）
		int i = 0;
		//创建表头
		HSSFCell cell = this.creatSheetHead(row,i,workbook);
		// = row.createCell((short) 0);
		// 定义单元格为字符串类型
		// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		// 设置cell编码解决中文高位字节截断
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);

		// 下面是不断从数据库中读数据到sheet中


		for (int j = 0; j < list.size(); j++) {
			//ChargeDatasDomain chargeDatasDomain = chargeDataslist.get(j);
			Short floor = (Short) list.get(j)[0];
			String houseNumber = (String) list.get(j)[1];
			i = 0;

			row = sheet.createRow((short) j + 1);
			// 设置建设期数
			cell = row.createCell((short) i++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellStyle(style);
			cell.setCellValue(projectName);
			// 设置楼栋编号
			cell = row.createCell((short) i++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellStyle(style);
			cell.setCellValue(buildingSeqNo);
			// 设置楼层
			cell = row.createCell((short) i++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			//flatDomain = (FlatDomain) chargeDatasDomain.getHouse();
			cell.setCellValue(floor);
			// 设置房号
			cell = row.createCell((short) i++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellStyle(style);
			cell.setCellValue(houseNumber);

			cell = row.createCell((short) i++);
			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(list.get(j)[2]==null?0.00:((BigDecimal)list.get(j)[2]).doubleValue());

			// 设置空的本月读数
			cell = row.createCell((short) i++);
			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("");
			// 设置当前的抄表时间
			cell = row.createCell((short) i++);
			HSSFCellStyle style1 = workbook.createCellStyle();
			//cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		    style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直对齐
		    style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平对齐
		    style1.setFont(font);
			cell.setCellStyle(style1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(currentDateStr);

		}
		return sheet;
	}

    private Map getmeterBases(Long buildingId,String meterType){

    	List<Object[]> meterBaseList = meterDao.getMeterBaseByBuildingId(buildingId, meterType == "水表"?"001":"002");

    	if(meterBaseList.size() > 0 ){
    		Map<Object, Object> map = new HashMap<Object, Object>();
    		for(Object[] object : meterBaseList){
    			map.put(object[0].toString(), object[1]);
        	}
    		return map;
    	}
    	return null;
    }

    private Map getmeterDatas(Long buildingId,String meterType){

    	List<Object[]> meterDataList = meterDataRegisterDao.getMeterDatasListByBuildingId(buildingId, meterType == "水表"?"001":"002");

    	if(meterDataList.size() > 0 ){
    		Map<Object, Object> map = new HashMap<Object, Object>();
    		for(Object[] object : meterDataList){
    			map.put(object[0].toString(), object[1]);
        	}
    		return map;
    	}
    	return null;
    }

    private List<Object[]> createBaseData(Long buildingId, List<Object[]> houselist,String meterType){


    	List<Object[]> list = new ArrayList<Object[]>();

    	Map meterBaseMap = this.getmeterBases(buildingId, meterType);

    	Map meterDataMap = this.getmeterDatas(buildingId, meterType);

    	for(Object[] objects :houselist){

    		// 设置表底数
			BigDecimal thisReading = null;
			BigDecimal metetData = null;
			BigDecimal meterBase = (BigDecimal) meterBaseMap.get(objects[0].toString());
			if(!BlankUtil.isBlank(meterDataMap)){
				metetData = (BigDecimal) meterDataMap.get(objects[0].toString());
			}
			thisReading = metetData==null?meterBase:metetData;

			Object[] obj = new Object[]{objects[3],objects[2],thisReading};

			list.add(obj);
		}
    	return list;
    }

    /**
	 * 方法描述: 创建工作簿并导出
	 * @author 胡煦
	 * Create on:2008-8-26
	 * @param Long buildingId
	 * @return HSSFWorkbook
	 * Edit author:
	 * Edit on:
	 * 修改原因：
	 */
    public HSSFWorkbook createHSSFWorkbook(Long buildingId){

//			List<ChargeDatasDomain> chargeDataslist = meterDataRegisterDao
//					.getMeterDatasListByBuildingId(buildingId);
    		List<Object[]> houselist1 = flatDao.getHouseListHasByBuildingId(buildingId,"001");
    		List<Object[]> houselist2 = flatDao.getHouseListHasByBuildingId(buildingId,"002");
			BuildingDomain buildingDomain = buildingDao.findById(buildingId);
			ProjectDomain projectDomain = buildingDomain.getProject();
			Integer buildingSeqNo = buildingDomain.getSeqNo();
			String projectName = projectDomain.getName();

			// 创建新的Excel 工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			Date nowDate = this.getDate();
			String currentDateStr = DateUtil.convertDateToStr(nowDate, "yyyy-MM-dd");

			//Date currentDate = DateUtil.convertStrToDate(dateStr, "yyyy-MM-dd");

			if(!BlankUtil.isBlank(houselist2)){
				List<Object[]> list1 = createBaseData(buildingId, houselist2, "电表");
				HSSFSheet sheet1 = this.creatSheet(list1, projectName,
						buildingSeqNo, "电表", workbook,currentDateStr);
			}
			if(!BlankUtil.isBlank(houselist1)){
				List<Object[]> list2 = createBaseData(buildingId, houselist1, "水表");
				HSSFSheet sheet2 = this.creatSheet(list2, projectName,
						buildingSeqNo, "水表", workbook,currentDateStr);
			}
			return workbook;
	}
    //private ThreadLocal<HSSFWorkbook> wb=new ThreadLocal<HSSFWorkbook>();
    private IBuildingDao buildingDao;
    private IMeterDataRegisterDao meterDataRegisterDao;
    private IMeterDao meterDao;
    private IFlatDao flatDao;
	/**
	 * @param flatDao the flatDao to set
	 */
	public void setFlatDao(IFlatDao flatDao) {
		this.flatDao = flatDao;
	}

	/**
	 * @param meterDao the meterDao to set
	 */
	public void setMeterDao(IMeterDao meterDao) {
		this.meterDao = meterDao;
	}

	/**
	 * @param buildingDao the buildingDao to set
	 */
	public void setBuildingDao(IBuildingDao buildingDao) {
		this.buildingDao = buildingDao;
	}

	/**
	 * @param meterDataRegisterDao the meterDataRegisterDao to set
	 */
	public void setMeterDataRegisterDao(IMeterDataRegisterDao meterDataRegisterDao) {
		this.meterDataRegisterDao = meterDataRegisterDao;
	}


}